﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SqlCommandHelper
{
    public static class SqlServerExtensions
    {
        /// <summary>
        /// Set Parameter into SqlCommand
        /// </summary>
        /// <param name="command">Sql command</param>
        /// <param name="name">Parameter name</param>
        /// <param name="dbType">Sql db type</param>
        /// <param name="direction">Sql parameter direction : input or output</param>
        /// <param name="value">Parameter value</param>
        public static void SetParameter(this SqlCommand command, string name, DbType dbType, ParameterDirection direction, object value)
        {
            SqlParameter parameter;
            if (command.Parameters.Contains(name))
            {
                parameter = command.Parameters[name];
            }
            else
            {
                parameter = new SqlParameter();
                parameter.ParameterName = name;
                parameter.DbType = dbType;
                parameter.Direction = direction;
                command.Parameters.Add(parameter);
            }
            parameter.Value = value == null ? DBNull.Value : value;
        }

        /// <summary>
        /// Set prameters into command
        /// </summary>
        /// <typeparam name="T">TModel</typeparam>
        /// <param name="command">Sql command</param>
        /// <param name="entity">the Entity</param>
        public static StoredParameterBuilder<T> SetParameters<T>(this SqlCommand command, T entity)
            where T : class
        {
            return new StoredParameterBuilder<T>(command, entity);
        }

        /// <summary>
        /// Map IDataReader to List of TEntity
        /// </summary>
        /// <typeparam name="T">TEntity</typeparam>
        /// <param name="reader">IDataReader</param>
        /// <returns>List of TEntity</returns>
        public static IList<T> MapToEntityList<T>(this IDataReader reader)
        {
            IList<T> list = new List<T>();
            T entity = default(T);

            while (reader.Read())
            {
                entity = reader.MapToEntity<T>();
                list.Add(entity);
            }
            return list;
        }

        /// <summary>
        /// Map IDataReader to TEntity
        /// </summary>
        /// <typeparam name="T">TEntity</typeparam>
        /// <param name="reader">IDataReader</param>
        /// <returns>TEntity</returns>
        public static T MapToEntity<T>(this IDataReader reader)
        {
            T entity = default(T);
            entity = Activator.CreateInstance<T>();
            StoredValueInvoker sqlValueInvoker = new StoredValueInvoker(reader);

            foreach (PropertyInfo property in entity.GetType().GetProperties())
            {
                //Get DbField 
                DbField dbField = (DbField)property.GetCustomAttribute(typeof(DbField));
                if (dbField != null)
                {
                    string columnName = !string.IsNullOrEmpty(dbField.CustomName) ? dbField.CustomName : property.Name;
                    var dbValue = reader.GetValue(columnName);
                    if (dbValue != null && !Equals(dbValue, DBNull.Value))
                    {
                        object value = sqlValueInvoker.Invoke(property.PropertyType, columnName);
                        property.SetValue(entity, value, null);
                    }
                }
            }
            return entity;
        }

        /// <summary>
        /// Get String from SqlDataReader
        /// </summary>
        /// <param name="reader">Current IDataReader</param>
        /// <param name="name">parameter name</param>
        /// <returns>string</returns>
        public static string TryGetString(this IDataReader reader, string name)
        {
            return reader[name].ToString();
        }

        /// <summary>
        /// Get Int32 from SqlDataReader
        /// </summary>
        /// <param name="reader">Current SqlDataReader</param>
        /// <param name="name">parameter name</param>
        /// <returns>Int32</returns>
        public static int TryGetInt32(this IDataReader reader, string name)
        {
            int value;
            int.TryParse(reader[name].ToString(), out value);
            return value;
        }

        /// <summary>
        /// Get Int64 from IDataReader
        /// </summary>
        /// <param name="reader">Current IDataReader</param>
        /// <param name="name">parameter name</param>
        /// <returns>Int64</returns>
        public static Int64 TryGetInt64(this IDataReader reader, string name)
        {
            Int64 value;
            Int64.TryParse(reader[name].ToString(), out value);
            return value;
        }

        /// <summary>
        /// Get Guid from IDataReader
        /// </summary>
        /// <param name="reader">Current IDataReader</param>
        /// <param name="name">parameter name</param>
        /// <returns>Guid</returns>
        public static Guid TryGetGuid(this IDataReader reader, string name)
        {
            Guid value;
            Guid.TryParse(reader[name].ToString(), out value);
            return value;
        }

        /// <summary>
        /// Get bool from IDataReader
        /// </summary>
        /// <param name="reader">Current IDataReader</param>
        /// <param name="name">parameter name</param>
        /// <returns>bools</returns>
        public static bool TryGetBoolean(this IDataReader reader, string name)
        {
            bool value;
            bool.TryParse(reader[name].ToString(), out value);
            return value;
        }

        /// <summary>
        /// Get DateTime from IDataReader
        /// </summary>
        /// <param name="reader">Current SqlDataReader</param>
        /// <param name="name">parameter name</param>
        /// <returns>bools</returns>
        public static DateTime TryGetDateTime(this IDataReader reader, string name)
        {
            DateTime value;
            DateTime.TryParse(reader[name].ToString(), out value);
            return value;
        }

        /// <summary>
        /// Get double from IDataReader
        /// </summary>
        /// <param name="reader">Current SqlDataReader</param>
        /// <param name="name">parameter name</param>
        /// <returns>doubl</returns>
        public static double TryGetDouble(this IDataReader reader, string name)
        {
            double value;
            double.TryParse(reader[name].ToString(), out value);
            return value;
        }

        /*=================================================================
         If the object with the parameter name dosn't exist throws an exception 
         and return Null.
        ====================================================================*/
        private static object GetValue(this IDataReader reader, string prameterName)
        {
            try
            {
                return reader[prameterName];
            }
            catch (Exception)
            {
                return null;
            }
        }
    }
}
